Introduction to the PostgreSQL tutorial
In this article, we will have a tutorial to learn PostgreSQL. According to Statista, PostgreSQL is the 4th most popular database in the world (the other ones are Oracle, SQL Server, and MySQL). That is why we think it is an important DBMS to learn.
This tutorial is oriented toward newbies and contains the following sections:
- Create tables using SQL
- Create tables using the pgAdmin
- How to insert data in a table
- How to edit a table in pgAdmin
- How to modify (alter) a table using SQL
- How to update the table’s data with pgAdmin
- How to delete a row in pgAdmin
- How to update data using SQL
- How to delete data using SQL
- Working with JOIN in Postgres
- Tutorial to learn how to create views in PostgreSQL using pgAdmin
- How to view or edit data in Views using pgAdmin
- How to modify (alter) a view in PostgreSQL
- How to create a view using SQL
- Postgres in Azure
Requirements
- First, we need PostgreSQL installed.
- Secondly, pgAdmin installed.
- Finally, for the Azure section, we need an internet connection and an Azure account.
Tutorial to learn PostgreSQL – Create tables using SQL
For this tutorial, I am assuming that you are already connected to pgAdmin. If not, follow the instructions in the requirements.
First, to create a query go to Databases>postgres>Schemas, right-click and select Query Tool.
The following query creates a table named customers.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE customers ( id integer PRIMARY KEY, Firstname VARCHAR ( 50 ) NOT NULL, Lastname VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) NOT NULL, birthday DATE NOT NULL ); |
ID is an identifier and it is also the primary key. The primary key is used to uniquely identify the different records in the table. Ideally, it is an integer number.
Also, we have the varchar which is used to store characters. The first name, last na,me and email columns use this data type for string characters. The limit used is 50 characters for the firstname and lastname, and the email supports 255 characters.
Finally, the birthday column contains dates. It contains the birthday.
For more information about PostgreSQL data types refer to this link:
Tutorial to learn PostgreSQL – Create tables using the pgAdmin
Alternatively, you can create a Table using the pgAdmin. The pgAdmin is like the SSMS. You can administer the database, create, and edit tables. Modify the data using the UI. The following steps will help you to create your table using the pgAdmin:
First, go to the Browser and then go to Databases>postgres>Schemas>public>Tables, right-click and select the Create>Table option.
In the General tab, write the table’s name and then go to Columns.
You can use the + icon to add multiple columns. In this example, we are adding the id with the integer data type. Also, we set this column as a primary key.
The firstname and lastname use the character varying data type and the length equal to 50.
Finally, press the Save button.
How to insert data in a table
First, in order to insert data in the table, we will use the insert statement. The following sentences will insert 1 row in the customer table previously created.
|
1 2 3 4 5 |
INSERT INTO customers Values (1,'Jane','Fonda','jfonda@gmail.com','1937-12-21'); |
To insert data, INSERT INTO is used followed by the table name. Also, you use the word values and in parenthesis, you write the numbers without quotes and then the name, lastname, and email using quotes for strings and then the date using the YYYY-MM-DD format.
Secondly, to insert 3 values with the insert sentence, use the following sentences.
|
1 2 3 4 5 6 7 8 |
INSERT INTO customers Values (2,'Raquel','Welch','rwelch@gmail.com','1940-09-05'), (3,'Sophia','Loren','sloren@gmail.com','1934-09-20'), (4,'Elizabeth','Taylor','etaylor@gmail.com','1932-02-27') ; |
To insert multiple rows, you need to write the values in parentheses separated by commas for each row.
Tutorial to learn PostgreSQL – How to edit a table in pgAdmin
To modify the table in pgAdmin follow these steps. First, right-click the table that you want to modify and select Properties…
Go to the Columns tab and you can add columns using the + icon, edit columns using the pencil and delete columns using the basket icon to delete. Finally, press the Save button.
How to modify (alter) a table using SQL
Alternatively, you can use SQL to modify the tables.
The alter statement is used to modify a table. Let’s take a look at some examples.
First, we will add a column named age of type integer to the customer’s table.
|
1 2 3 4 |
ALTER TABLE public.customers ADD COLUMN age integer; |
Optionally, we can use the IF EXISTS condition to execute the sentence only if the table exists.
|
1 2 3 4 |
ALTER TABLE IF EXISTS public.customers ADD COLUMN age integer; |
Secondly, to drop the column age, use the following sentence:
|
1 2 3 4 |
ALTER TABLE customers DROP COLUMN age |
If you need to verify if the table and the column exist, you can use the following sentences.
|
1 2 3 4 |
ALTER TABLE IF EXISTS public.customers DROP COLUMN IF EXISTS age; |
For more information about the ALTER sentence, you can read the following link:
How to update the table’s data with pgAdmin
In pgAdmin right-click the table and select View/Edit data. You can edit all rows or work with the first, and last rows, or use filters. If there aren’t so many rows, the all-rows option is OK. If there are many rows in the table, you may need to work with other options.
Double-click the cell that you want to modify.
Write the new value for the cell and press OK.
There is an option to save changes.
How to delete a row in pgAdmin
In pgAdmin right-click the table and select View/Edit data>All rows.
Select the row or rows to delete and press the Delete icon and then save the changes.
How to update data using SQL
If you are new in this world, you may wonder why on earth should you learn to handle databases using SQL if you can use the pgAdmin. Sometimes you need to automate tasks of integrating your database with other tools and it is necessary to work with SQL.
The following example shows how to replace the value Sophia to Sofia in the column firstname of the table customers.
|
1 2 3 4 |
UPDATE customers SET firstname = 'Sofia' WHERE firstname = 'Sophia';</strong> |
How to delete data using SQL
Now, we will show how to delete the data. The following example will delete the row of the table customers where the id is equal to 2.
|
1 2 3 4 |
DELETE FROM customers WHERE id=2; |
Working with JOIN in Postgres
The joins are used to get information from 2 or more tables. There are several types of JOINS like the INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.
We created an article for you related to this topic.
Tutorial to learn how to create views in PostgreSQL using pgAdmin
A view in Postgres is a virtual table that stores a query of one or more tables. In pgAdmin, go to the Database and look for the Views. Right-click and select Create>View.
In the General tab write a name for the view and go to the Code tab.
In the Code tab, write the following query.
|
1 2 3 4 |
Select lastname From customers |
Finally, press Save.
How to view or edit data in Views using pgAdmin
You can edit or view data in PostgreSQL views in the same way that you do in tables. Right-click the view and select View/Edit Data>All rows.
How to modify (alter) a view in PostgreSQL
To modify a view in PostgreSQL, right-click on the view to alter and select the Properties.
Go to the Code tab and modify the query according to your need and press Save.
How to create a view using SQL
To create a view in Postgres using SQL, you need to use the create sentence. The following example creates a view named v_customers and shows the lastname of the table customers.
|
1 2 3 4 5 6 |
Create view v_customers as Select lastname From customers; |
How to edit (alter) a view using SQL. Also, you can modify a view using the alter statement.
The following example will modify an existing view named v_customer.
|
1 2 3 4 5 6 |
Alter view v_customers as Select lastname,email From customers; |
Postgres in Azure
Azure offers a cloud service. This is a Microsoft Service. We created some nice tutorials to work with Azure and Postgres:
- Managing schema in Azure Database for PostgreSQL using pgAdmin
- Querying data from Azure Database for PostgreSQL using psql
- Getting started with procedures in Azure Database for PostgreSQL
- Reporting data from Azure Database for PostgreSQL using Power BI
- Working with views in Azure Database for PostgreSQL
- Configure ODBC drivers for Azure Database for PostgreSQL
Conclusion
Learning Postgres is a straightforward process if you already worked with other DBMS tools. In this tutorial to learn PostgreSQL, we learned to work with pgAdmin which is a tool similar to SSMS, and also, we learned to use SQL which is similar to the SQL used in SQL Server.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023